package com.etooling.framework.utils;


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class DBUtils {


    public void importData(String sql, Connection conn) throws SQLException {
        conn.setAutoCommit(false);
        PreparedStatement pstmt = conn.prepareStatement(sql);

        close(pstmt);
    }

    public boolean chkTable(String tableName, Connection conn) {
        int cnt = 0;
        String sql = "select count(*) from user_tables where upper(table_name) = ?";
        Object[] args = {tableName};
        try {
            cnt = queryForInt(sql, args, conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return cnt > 0;
    }

    public long queryForLong(String sql, Connection conn) throws SQLException {
        long ret = 0L;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                ret = rs.getLong(1);
            }
        } finally {
            close(stmt, rs);
        }
        return ret;
    }


    public long queryForLong(String sql, List<Object> argList, Connection conn) throws SQLException {
        long ret = 0L;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            setPstmt(pstmt, argList);
            rs = pstmt.executeQuery(sql);
            if (rs.next()) {
                ret = rs.getLong(1);
            }
        } finally {
            close(pstmt, rs);
        }
        return ret;
    }


    public void truncateTable(String tableName, Connection conn) throws SQLException {
        if (WebUtils.checkStr(tableName) && chkTable(tableName, conn)) {
            String sql = "truncate table " + tableName + " drop storage";
            Statement stmt = null;
            try {
                stmt = conn.createStatement();
                stmt.executeUpdate(sql);
            } finally {
                close(stmt);
            }
        }
    }

    public void dropTable(String tableName, Connection conn) throws SQLException {
        if (WebUtils.checkStr(tableName) && chkTable(tableName, conn)) {
            String sql = "drop table " + tableName + " purge";
            Statement stmt = null;
            try {
                stmt = conn.createStatement();
                stmt.execute(sql);
            } finally {
                close(stmt);
            }
        }
    }


    @Autowired
    private JdbcTemplate jdbcTemplate;
    public List<String> queryForStringList(String sql, Connection conn) throws SQLException {
        List<String> list = new ArrayList<String>();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                list.add(rs.getString(1));
            }
        } finally {
            close(stmt, rs);
        }
        return list;
    }

    public List<String> queryForStringList(String sql, Object[] args, Connection conn) throws SQLException {
        List<String> list = new ArrayList<String>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            setPstmt(pstmt, args);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                list.add(rs.getString(1));
            }
        } finally {
            close(pstmt, rs);
        }
        return list;
    }

    public void execute(String sql, Object[] args, Connection conn) throws SQLException {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            setPstmt(pstmt, args);
            pstmt.executeUpdate();
        } finally {
            close(pstmt);
        }
    }

    public Integer execute(String sql, Connection conn) throws SQLException {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            Integer i = pstmt.executeUpdate();
            return i;
        } finally {
            close(pstmt);
        }
    }

    // public List<Model> getModels(String sql, Object[] args) {
    // return jdbcTemplate.query(sql, args, new ModelRowMapper());
    // }


    public void close(Statement stmt, ResultSet rs) {
        close(rs);
        close(stmt);
    }


    public void deleteById(String tableName, String pk, Object id) {
        if (id == null || "".equals(id.toString().trim())) {
            return;
        }
        String sql = "delete from " + tableName + " where " + pk + " = ?";
        Object[] args = {id};
        jdbcTemplate.update(sql, args);
    }

    public List<String> queryStringList(String sql) {
        return jdbcTemplate.query(sql, new StringRowMapper());
    }

    public List<String> queryStringList(String sql, Object[] args) {
        return jdbcTemplate.query(sql, args, new StringRowMapper());
    }

    public String queryForString(String sql, Object[] args) {
        String[] ids = queryStringArray(sql, args);
        if (ids.length != 0) {
            return ids[0];
        }
        return null;
    }

    public String queryForString(String sql) {
        String[] ids = queryStringArray(sql);
        if (ids.length != 0) {
            return ids[0];
        }
        return null;
    }

    public String[] queryStringArray(String sql) {
        return convert(queryStringList(sql));
    }

    public String[] queryStringArray(String sql, Object[] args) {
        return convert(queryStringList(sql, args));
    }

    private String[] convert(List<String> list) {
        String[] ret = new String[list.size()];
        for (int i = 0; i < ret.length; i++) {
            ret[i] = list.get(i);
        }
        return ret;
    }

    private class StringRowMapper implements RowMapper {

        public String mapRow(ResultSet rs, int rowNum) throws SQLException {
            return rs.getString(1);
        }

    }

    public void close(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public int executeSql(String sql, Connection conn) throws SQLException {
        int count = 0;
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            count = pstmt.executeUpdate();
        } finally {
            close(pstmt);
        }
        return count;
    }

    public int executeSql(String sql, Object[] args, Connection conn) throws SQLException {
        int count = 0;
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            setPstmt(pstmt, args);
            count = pstmt.executeUpdate();
        } finally {
            close(pstmt);
        }
        return count;
    }

    public static void close(PreparedStatement pstmt) {
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(PreparedStatement pstmt, ResultSet rs) {
        close(pstmt);
        close(rs);
    }

    public static void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void setPstmt(PreparedStatement pstmt, List<Object> argList) throws SQLException {
        if (argList == null || argList.size() == 0) {
            return;
        }
        for (int i = 0; i < argList.size(); i++) {
            pstmt.setObject(i + 1, argList.get(i));
        }
    }

    public void setPstmt(PreparedStatement pstmt, Object[] args) throws SQLException {
        if (args == null || args.length == 0) {
            return;
        }
        for (int i = 0; i < args.length; i++) {
            pstmt.setObject(i + 1, args[i]);
        }
    }

    public void executeSql(String sql) {
        jdbcTemplate.update(sql);
    }

    public void executeSql(String sql, Object[] args) {
        jdbcTemplate.update(sql, args);
    }

    public int queryForInt(String sql, Connection conn) throws SQLException {
        int count = 0;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } finally {
            close(stmt, rs);
        }
        return count;
    }

    public int queryForInt(String sql, Object[] args, Connection conn) throws SQLException {
        int count = 0;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            setPstmt(pstmt, args);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } finally {
            close(pstmt, rs);
        }
        return count;
    }

    public int queryForInt(String sql, List<Object> argList, Connection conn) throws SQLException {
        int count = 0;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            setPstmt(pstmt, argList);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } finally {
            close(pstmt, rs);
        }
        return count;
    }

    public String queryForString(String sql, List<Object> argList, Connection conn) throws SQLException {
        String value = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            setPstmt(pstmt, argList);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                value = rs.getString(1);
            }
        } finally {
            close(pstmt, rs);
        }
        return value;
    }

    public String queryForString(String sql, Object[] args, Connection conn) throws SQLException {
        String value = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            setPstmt(pstmt, args);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                value = rs.getString(1);
            }
        } finally {
            close(pstmt, rs);
        }
        return value;
    }

    public String queryForString(String sql, Connection conn) throws SQLException {
        String value = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                value = rs.getString(1);
            }
        } finally {
            close(stmt, rs);
        }
        return value;
    }
 

    public void close(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        close(rs);
        close(pstmt);
        close(conn);
    }

    public int executeUpdate(String sql, List<Object> argList, Connection conn) throws SQLException {
        int cnt = -1;
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            setPstmt(pstmt, argList);
            cnt = pstmt.executeUpdate();
        } finally {
            close(pstmt);
        }
        return cnt;
    }

    public int executeUpdate(String sql, Object[] args, Connection conn) throws SQLException {
        int cnt = -1;
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            setPstmt(pstmt, args);
            cnt = pstmt.executeUpdate();
        } finally {
            close(pstmt);
        }
        return cnt;
    }

    public void close(Connection conn, Statement stmt, ResultSet rs) {
        close(rs);
        close(stmt);
        close(conn);
    }

}
